/***
    Amrita ITEWS - Copyright (C) 2009 Amrita Vishwa Vidyapeetham, Amritapuri.
                                       (http://www.amrita.edu)
    ***************************************************************************
    This file is part of the Amrita ITEWS distribution.
    Amrita ITEWS is free software; you can redistribute it and/or modify it 
    under the terms of the GNU General Public License (version 2) as published
    by the Free Software Foundation AND MODIFIED BY the Amrita ITEWS exception.
    ***NOTE*** The exception to the GPL is included to allow you to distribute
    a combined work that includes Amrita ITEWS without being obliged to provide
    the source code for proprietary components outside of the Amrita ITEWS
    software. Amrita ITEWS is distributed in the hope that it will be useful, 
    but WITHOUT ANY WARRANTY; without even the implied warranty of 
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
    Public License for more details. You should have received a copy of the GNU
    General Public License and the Amrita ITEWS license exception along with 
    Amrita ITEWS if not then it can be viewed here: 
    http://itews.amrita.ac.in/license.html.

    Documentation, latest information, license and contact details are at:
    http://itews.amrita.ac.in/

    Amrita ITEWS source code can be found at:
    http://code.google.com/p/itews

    The initial code-base of Amrita ITEWS was developed by Amrita Vishwa 
    Vidyapeetham as part of the project titled,"Intelligent & Interactive 
    Telematics using Emerging Wireless Technologies for Transport Systems 
    (ITEWS)" sponsored by Technology Information Forecasting and Assessment 
    Council (TIFAC), India.
***/

package tdcsinfoservice;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import org.knopflerfish.service.log.LogRef;
import org.osgi.service.event.Event;

public class MediaInfoDatabaseUpdater
{
	private static final String INFO_TYPE = "info_type";
	private static final String MEDIA = "media";
	private static final String PLAN = "media_plan";
	private static final String ROUTE_SCHED_CODE = "route_sched_code";
	private static final String PLAN_DETAILS = "plan_details";
	private static final String MEDIA_DESC_TYPE = "MD";
	private static final String TRIGGER_DESC_TYPE = "TD";
	private static final String MEDIA_CODE = "media_code";
	private static final String MEDIA_TITLE = "media_title";
	private static final String MEDIA_DURATION = "media_dur";
	private static final String MEDIA_PATH = "media_path";
	private static final String MEDIA_LANGUAGE_CODE = "media_language_code";
	private static final String NODE_CODE = "node_code";
	private static final String TRIGGER_CONDITION = "trigger_condition"; 
	private static final String LOOP_TILL_NEXT_TRIGGER_FLAG = "loop_till_next_trigger_flag";
	private static final String MEDIA_COMPONENT_LIST = "media_comp_list";
	private static final String CONTROL_PARAM = "control_param";
	private static final String DELETE_OPT = "delete";
	private static final String TYPE = "type";
	private static final String MEDIA_TYPE = "media_type";
	private static final String MEDIA_CODE_OR_DURATION = "media_code_or_duration";
	private static final String NORMAL_MEDIA = "NM";
	private DBConnectionPool dbConnectionPool ;
	private TDCSInfoServiceConfig config;
	private  LogRef log;
	private String routeSchedulesQuery = "SELECT id FROM route_schedules WHERE route_schedule_code=?";
	private String routeSchedulesPartialInsert = "INSERT INTO route_schedules VALUES(default, ?, '', 0, 'F', 'F')";
	private String routeSchedulesUpdate = "UPDATE route_schedules SET is_download_pending='T' WHERE route_schedule_code=?";
	private String nodesQuery = "SELECT id FROM nodes WHERE node_code=?";
	private String nodesInsertPartial = "INSERT INTO nodes VALUES(default, ?, 999, 999, GeomFromText('Polygon((0 0))'))";
	private String mediaDescriptorsQuery = "SELECT media_duration, relative_media_path, absolute_media_path, percentage_downloaded FROM media_descriptors WHERE media_code=?";
	private String mediaDescriptorsInsert = "INSERT INTO media_descriptors VALUES(default,?,?,?,?,'',?,default,default,default,default,default,default)";
	private String mediaDescriptorsUpdate = "UPDATE media_descriptors SET media_title=?, media_duration=?, relative_media_path=?, media_language_code=?, percentage_downloaded=0, last_known_status='init' WHERE media_code=?";
	private String mediaDescriptorsUpdateWithoutDownloadReset = "UPDATE media_descriptors SET media_title=?, media_duration=?, media_language_code=? WHERE media_code=?";
	private String mediaDescriptorsDelete = "DELETE FROM media_descriptors WHERE media_code=?";
	private String triggerDescriptorsDelete = "DELETE FROM trigger_descriptors WHERE route_schedule_code=? AND node_code=?";
	private String triggerDescriptorsInsert = "INSERT INTO trigger_descriptors VALUES(default,?,?,?,?)";
	private String triggerDescriptorsQuery = "SELECT node_code FROM trigger_descriptors WHERE route_schedule_code=?";
	private String selectLastInsertId = "SELECT MAX(id) FROM trigger_descriptors"; 
	private String triggerDescMediaComponentsInsert = "INSERT INTO trigger_desc_media_components VALUES(default, ?, ?, ?, ?, ?)";
	private String triggerDescMediaComponentsDelete = "DELETE FROM trigger_desc_media_components WHERE media_code=?";
	private String garbageMediaInsert = "INSERT INTO garbage_media VALUES(default, ?, default)";
	private String prevSuccessfulDownloadQuery= "SELECT media_code FROM previously_successfully_downloaded_medias WHERE media_code=?";
	private String prevSuccessfulDownloadUpdate = "UPDATE previously_successfully_downloaded_medias SET relative_media_path=?, absolute_media_path=?, media_duration=? WHERE media_code=?";
	private String prevSuccessfulDownloadInsert = "INSERT INTO previously_successfully_downloaded_medias VALUES (?,?,?,?)";
	private String prevSuccessfulDownloadDelete = "DELETE FROM previously_successfully_downloaded_medias WHERE media_code=?";
	
	public MediaInfoDatabaseUpdater()
	{
		try
		{
			log = new LogRef(Activator.bc);
			config = TDCSInfoServiceConfig.getConfig();
			dbConnectionPool = DBConnectionPool.getInstance(config.getJdbcConnectionURI());
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public synchronized void updateMediaPlanDatabase(Event event)
	{
		Connection conn = null;
		PreparedStatement preparedStmt = null;
		PreparedStatement preparedStmtA = null;
		PreparedStatement preparedStmt1 = null;
		PreparedStatement preparedStmt2 = null;
		PreparedStatement preparedStmt3 = null;
		PreparedStatement preparedStmt3A = null;
		PreparedStatement preparedStmt3B = null;
		PreparedStatement preparedStmt4 = null;
		PreparedStatement preparedStmt5 = null;
		PreparedStatement preparedStmt6 = null;
		PreparedStatement preparedStmt6A = null;
		PreparedStatement preparedStmt6B = null;
		PreparedStatement preparedStmt7 = null;
		PreparedStatement preparedStmt8 = null;
		PreparedStatement preparedStmt9 = null;
		PreparedStatement preparedStmt10 = null;
		PreparedStatement preparedStmt10A = null;
		PreparedStatement preparedStmt10B = null;
		PreparedStatement preparedStmt11 = null;
		PreparedStatement preparedStmt11A = null;
		PreparedStatement preparedStmt12 = null;
		PreparedStatement preparedStmt12A = null;
		PreparedStatement preparedStmt12B = null;
		PreparedStatement preparedStmt13 = null;

		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3A = null;
		ResultSet rs6 = null;
		ResultSet rs6A = null;
		ResultSet rs8 = null;
		ResultSet rs9 = null;
		ResultSet rs11 = null;
		ResultSet rs12A = null;
		try 
		{
			String routeScheduleCode = null;
			String infoType = (String) event.getProperty(INFO_TYPE);
			System.out.println("infoType in Event received at MediaDabaseUpdater: "+infoType);
			conn = dbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			ArrayList mediaScheduledNodes = new ArrayList();
			if (infoType.equals(PLAN))
			{
				
				if (event.getProperty(ROUTE_SCHED_CODE) == null)
				{
					throw new Exception("Aborted Media Plan Database update. Route Schedule Code not available");
				}
				else
				{
					routeScheduleCode = ((String) event.getProperty(ROUTE_SCHED_CODE)).trim();
					if (routeScheduleCode.equals(""))
					{
						throw new Exception("Aborted Media Plan Database update. Route Schedule Code not available");
					}
				}
				Object planDetailsObj = event.getProperty(PLAN_DETAILS);
				if (planDetailsObj == null || ((ArrayList)planDetailsObj).size() == 0)
				{
					throw new Exception("Aborted Media Plan Database update. Plan Details for Route Schedule Code not available");
				}
				preparedStmt = dbConnectionPool.getPreparedStmt(conn, routeSchedulesQuery);
				preparedStmt.setString(1, routeScheduleCode);
				rs = preparedStmt.executeQuery();
				if (rs.next() == false)
				{
					preparedStmtA = dbConnectionPool.getPreparedStmt(conn, routeSchedulesPartialInsert);
					preparedStmtA.setString(1, routeScheduleCode);
					preparedStmtA.executeUpdate();
					preparedStmtA.close();
					preparedStmtA = null;
					System.out.println("Route Code not yet available while handling mediainfo feed. Did a partial insert in route_schedules table. Route Code: "+routeScheduleCode);
				}
				rs.close(); 
				rs = null;
				preparedStmt.close();
				preparedStmt = null;
				
				ArrayList planDetailsList = (ArrayList) planDetailsObj;
				for(int i=0; i< planDetailsList.size(); i++)
				{
					Object attr=null;
					String type=null;
					int mediaDur=0;
					String mediaCode=null, mediaTitle=null, mediaPath=null, mediaLangCode=null;
					String nodeCode=null, triggerCondition=null, loopTillNextTriggerFlag=null;
					HashMap member = (HashMap) planDetailsList.get(i);
					attr = member.get(TYPE);
					if (attr != null) type = (String) attr;
					if (type.equals(MEDIA_DESC_TYPE))
					{
						attr = member.get(MEDIA_CODE);
						if (attr != null) mediaCode = (String) attr;
						
						attr = member.get(MEDIA_TITLE);
						if (attr != null) mediaTitle = (String) attr;
						
						attr = member.get(MEDIA_DURATION);
						if (attr != null) mediaDur = (Integer) attr;
						attr = member.get(MEDIA_PATH);
						if (attr != null) mediaPath = (String) attr;
						
						attr = member.get(MEDIA_LANGUAGE_CODE);
						if (attr != null) mediaLangCode = (String) attr;
						
						if (mediaCode!=null && mediaTitle!=null && mediaPath!=null && mediaLangCode!=null)
						{
							preparedStmt1 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsQuery);
							preparedStmt1.setString(1, mediaCode);
							rs1 = preparedStmt1.executeQuery();
							if (rs1.next() == false) // Insert into media_descriptors table
							{
								preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsInsert);
								preparedStmt2.setString(1, mediaCode);
								preparedStmt2.setString(2, mediaTitle);
								preparedStmt2.setInt(3, mediaDur);
								preparedStmt2.setString(4, mediaPath.trim());
								preparedStmt2.setString(5, mediaLangCode);
								preparedStmt2.executeUpdate();
								preparedStmt2.close();
								preparedStmt2 = null;
							}
							else // Update media_descriptors table
							{
								int mediaDuration = rs1.getInt(1);
								String previousRelativeMediaPath = rs1.getString(2);
								String previousAbsoluteMediaPath = rs1.getString(3);
								int percentageDownloaded = rs1.getInt(4);
								if (!previousRelativeMediaPath.trim().equals(mediaPath.trim()))
								{
									if (previousAbsoluteMediaPath != null)
									{
										if (percentageDownloaded == 100)
										{
											preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadQuery);
											preparedStmt2.setString(1, mediaCode);
											rs2 = preparedStmt2.executeQuery();
											if (rs2.next() == false)
											{
												preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadInsert);
												preparedStmt3.setString(1, mediaCode);
												preparedStmt3.setString(2, previousRelativeMediaPath);
												preparedStmt3.setString(3, previousAbsoluteMediaPath);
												preparedStmt3.setInt(4, mediaDuration);
												preparedStmt3.executeUpdate();
												preparedStmt3.close();
												preparedStmt3 = null;
											}
											else
											{
												preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadUpdate);
												preparedStmt3.setString(1, previousRelativeMediaPath);
												preparedStmt3.setString(2, previousAbsoluteMediaPath);
												preparedStmt3.setInt(3, mediaDuration);
												preparedStmt3.setString(4, mediaCode);
												preparedStmt3.executeUpdate();
												preparedStmt3.close();
												preparedStmt3 = null;
											}
											rs2.close();
											rs2 = null;
											preparedStmt2.close();
											preparedStmt2 = null;
										}
										preparedStmt3A = dbConnectionPool.getPreparedStmt(conn, garbageMediaInsert);
										preparedStmt3A.setString(1, previousAbsoluteMediaPath);
										preparedStmt3A.executeUpdate();
										preparedStmt3A.close();
										preparedStmt3A = null;
									}
									preparedStmt3B = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsUpdate);
									preparedStmt3B.setString(1, mediaTitle);
									preparedStmt3B.setInt(2, mediaDur);
									preparedStmt3B.setString(3, mediaPath);
									preparedStmt3B.setString(4, mediaLangCode);
									preparedStmt3B.setString(5, mediaCode);
									preparedStmt3B.executeUpdate();
									preparedStmt3B.close();
									preparedStmt3B = null;
								}
								else
								{
									preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsUpdateWithoutDownloadReset);
									preparedStmt3.setString(1, mediaTitle);
									preparedStmt3.setInt(2, mediaDur);
									preparedStmt3.setString(3, mediaLangCode);
									preparedStmt3.setString(4, mediaCode);
									preparedStmt3.executeUpdate();
									preparedStmt3.close();
									preparedStmt3 = null;
								}
							}
							rs1.close(); 
							rs1 = null;
							preparedStmt1.close();
							preparedStmt1 = null;
						} // Invalid Media Descriptor data in feed
						else
						{
							throw new Exception("Invalid Media Descriptor data in feed - " + 
									" mediaCode: " + mediaCode + " mediaTitle: " +mediaTitle +
									" mediaDur: " + mediaDur + " mediaPath: "+ mediaPath +
									" mediaLangCode: " + mediaLangCode);
						}
					}
					else if (type.equals(TRIGGER_DESC_TYPE))
					{
						attr = member.get(NODE_CODE);
						if (attr != null) nodeCode = (String) attr;
						if (nodeCode != "" && !mediaScheduledNodes.contains(nodeCode))
						{
							mediaScheduledNodes.add(nodeCode);
						}
						attr = member.get(TRIGGER_CONDITION);
						if (attr != null) triggerCondition = (String) attr;
						
						attr = member.get(LOOP_TILL_NEXT_TRIGGER_FLAG);
						if (attr != null) loopTillNextTriggerFlag = (String) attr;
						
						if (nodeCode!=null && triggerCondition!=null && loopTillNextTriggerFlag!=null)
						{
							preparedStmt3A = dbConnectionPool.getPreparedStmt(conn, nodesQuery);
							preparedStmt3A.setString(1, nodeCode);
							rs3A = preparedStmt3A.executeQuery();
							if (rs3A.next() == false)
							{
								preparedStmt3B = dbConnectionPool.getPreparedStmt(conn, nodesInsertPartial);
								preparedStmt3B.setString(1, nodeCode);
								preparedStmt3B.executeUpdate();
								preparedStmt3B.close();
								preparedStmt3B = null;
								System.out.println("Node not yet available while handling mediainfo feed. Did a partial insert in nodes table. Node Code: "+nodeCode);
							}
							rs3A.close();
							rs3A = null;
							preparedStmt3A.close();
							preparedStmt3A = null;
							
							preparedStmt4 = dbConnectionPool.getPreparedStmt(conn, triggerDescriptorsDelete);
							preparedStmt4.setString(1, routeScheduleCode);
							preparedStmt4.setString(2, nodeCode);
							preparedStmt4.executeUpdate();
							preparedStmt4.close();
							preparedStmt4 = null;
							
							preparedStmt5 = dbConnectionPool.getPreparedStmt(conn, triggerDescriptorsInsert);
							preparedStmt5.setString(1, routeScheduleCode);
							preparedStmt5.setString(2, nodeCode);
							preparedStmt5.setString(3, triggerCondition);
							preparedStmt5.setString(4, loopTillNextTriggerFlag);
							preparedStmt5.executeUpdate();
							preparedStmt5.close();
							preparedStmt5 = null;
							
							preparedStmt6 = dbConnectionPool.getPreparedStmt(conn, selectLastInsertId);
							rs6 = preparedStmt6.executeQuery();
							int currentTriggerDescriptorId = 0;
							if (rs6.next() == true)
							{
								currentTriggerDescriptorId = rs6.getInt(1);
							}
							rs6.close(); 
							rs6 = null;
							preparedStmt6.close();
							preparedStmt6 = null;
							
							Object mediaComponentListObj = member.get(MEDIA_COMPONENT_LIST);
							ArrayList mediaComponentList = null;
							if (mediaComponentListObj != null)
							{
								mediaComponentList = (ArrayList) mediaComponentListObj;
								if (mediaComponentList.size() == 0)
								{
									throw new Exception("Invalid Trigger Descriptor data in feed - No media Components"); 
								}
								else
								{
									int seqNo = 0;
									for (int j=0; j< mediaComponentList.size(); j++)
									{
										String triggerDescMediaType = null;
										String mediaCodeForNormalMedia = null;
										int mediaDurationForNotNormalMedia = 0;
										HashMap mediaComponentMember = (HashMap) mediaComponentList.get(j);
										attr = mediaComponentMember.get(MEDIA_TYPE);
										if (attr != null) triggerDescMediaType = (String) attr;
										attr = mediaComponentMember.get(MEDIA_CODE_OR_DURATION);
										if (attr != null) 
										{
											if(triggerDescMediaType.equals(NORMAL_MEDIA))
											{
												mediaCodeForNormalMedia = (String) attr;
											}
											else
											{
												mediaDurationForNotNormalMedia = (Integer) attr;
											}
										}
										if (triggerDescMediaType != null)
										{
											if (triggerDescMediaType.equals(NORMAL_MEDIA))
											{
												preparedStmt6A = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsQuery);
												preparedStmt6A.setString(1, mediaCodeForNormalMedia);
												rs6A = preparedStmt6A.executeQuery();
												if (rs6A.next() == false)
												{
													preparedStmt6B = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsInsert);
													preparedStmt6B.setString(1, mediaCodeForNormalMedia);
													preparedStmt6B.setString(2, "");
													preparedStmt6B.setInt(3, 0);
													preparedStmt6B.setString(4, "");
													preparedStmt6B.setString(5, "");
													preparedStmt6B.executeUpdate();
													preparedStmt6B.close();
													preparedStmt6B = null;
													System.out.println("Media Code details not yet available while handling mediainfo feed. Inserted a partial insert in media_descriptors table. Media Code: "+mediaCodeForNormalMedia);
												}
												rs6A.close();
												rs6A = null;
												preparedStmt6A.close();
												preparedStmt6A = null;

												preparedStmt7 = dbConnectionPool.getPreparedStmt(conn, triggerDescMediaComponentsInsert);
												preparedStmt7.setInt(1, currentTriggerDescriptorId);
												preparedStmt7.setString(2, triggerDescMediaType);
												preparedStmt7.setString(3, mediaCodeForNormalMedia);
												preparedStmt7.setInt(4, 0);
												preparedStmt7.setInt(5, seqNo);
												preparedStmt7.executeUpdate();
												seqNo++;
												preparedStmt7.close();
												preparedStmt7 = null;

											}
											else
											{
												preparedStmt7 = dbConnectionPool.getPreparedStmt(conn, triggerDescMediaComponentsInsert);
												preparedStmt7.setInt(1, currentTriggerDescriptorId);
												preparedStmt7.setString(2, triggerDescMediaType);
												preparedStmt7.setString(3, "");
												preparedStmt7.setInt(4, mediaDurationForNotNormalMedia);
												preparedStmt7.setInt(5, seqNo);
												preparedStmt7.executeUpdate();
												seqNo++;
												preparedStmt7.close();
												preparedStmt7 = null;
											}
										}
									}
								}
							}
							else
							{
								throw new Exception("Invalid Trigger Descriptor data in feed - No media Components"); 
							}
						}
						else
						{
							throw new Exception("Invalid Trigger Descriptor data in feed - " + 
									" nodeCode: " + nodeCode + " triggerCondition: " +triggerCondition +
									" loopTillNextTriggerFlag: "+ loopTillNextTriggerFlag);
						}
					}
				}

			}
			else if (infoType.equals(MEDIA))
			{
				Object controlParmObj = event.getProperty(CONTROL_PARAM);
				
				if (controlParmObj == null) // if medias table is to be inserted or updated
				{
					Object attr=null;
					int mediaDur=0;
					String mediaCode=null, mediaTitle=null, relativeMediaPath=null, mediaLangCode=null;
					attr = event.getProperty(MEDIA_CODE);
					if (attr != null) mediaCode = (String) attr;
					
					attr = event.getProperty(MEDIA_TITLE);
					if (attr != null) mediaTitle = (String) attr;
					
					attr = event.getProperty(MEDIA_DURATION);
					if (attr != null) mediaDur = (Integer) attr;

					attr = event.getProperty(MEDIA_PATH);
					if (attr != null) relativeMediaPath = (String) attr;
					
					attr = event.getProperty(MEDIA_LANGUAGE_CODE);
					if (attr != null) mediaLangCode = (String) attr;
					
					if (mediaCode!=null && mediaTitle!=null && mediaDur!=0 && relativeMediaPath!=null && mediaLangCode!=null)
					{
						preparedStmt8 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsQuery);
						preparedStmt8.setString(1, mediaCode);
						rs8 = preparedStmt8.executeQuery();
						if (rs8.next() == false) // Insert into media_descriptors table
						{
							preparedStmt9 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsInsert);
							preparedStmt9.setString(1, mediaCode);
							preparedStmt9.setString(2, mediaTitle);
							preparedStmt9.setInt(3, mediaDur);
							preparedStmt9.setString(4, relativeMediaPath);
							preparedStmt9.setString(5, mediaLangCode);
							preparedStmt9.executeUpdate();
							preparedStmt9.close();
							preparedStmt9 = null;
						}
						else // Update media_descriptors table
						{
							int mediaDuration = rs8.getInt(1);
							String previousRelativeMediaPath = rs8.getString(2);
							String previousAbsoluteMediaPath = rs8.getString(3);
							int percentageDownloaded = rs8.getInt(4);
							if (!previousRelativeMediaPath.trim().equals(relativeMediaPath.trim()))
							{
								if (previousAbsoluteMediaPath != null)
								{
									if (percentageDownloaded == 100)
									{
										preparedStmt9 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadQuery);
										preparedStmt9.setString(1, mediaCode);
										rs9 = preparedStmt9.executeQuery();
										if (rs9.next() == false)
										{
											preparedStmt10 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadInsert);
											preparedStmt10.setString(1, mediaCode);
											preparedStmt10.setString(2, previousRelativeMediaPath);
											preparedStmt10.setString(3, previousAbsoluteMediaPath);
											preparedStmt10.setInt(4, mediaDuration);
											preparedStmt10.executeUpdate();
											preparedStmt10.close();
											preparedStmt10 = null;
										}
										else
										{
											preparedStmt10 = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadUpdate);
											preparedStmt10.setString(1, previousRelativeMediaPath);
											preparedStmt10.setString(2, previousAbsoluteMediaPath);
											preparedStmt10.setInt(3, mediaDuration);
											preparedStmt10.setString(4, mediaCode);
											preparedStmt10.executeUpdate();
											preparedStmt10.close();
											preparedStmt10 = null;
										}
										rs9.close();
										rs9 = null;
										preparedStmt9.close();
										preparedStmt9 = null;
									}
									preparedStmt10A = dbConnectionPool.getPreparedStmt(conn, garbageMediaInsert);
									preparedStmt10A.setString(1, previousAbsoluteMediaPath);
									preparedStmt10A.executeUpdate();
									preparedStmt10A.close();
									preparedStmt10A = null;
								}
								
								preparedStmt10B = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsUpdate);
								preparedStmt10B.setString(1, mediaTitle);
								preparedStmt10B.setInt(2, mediaDur);
								preparedStmt10B.setString(3, relativeMediaPath);
								preparedStmt10B.setString(4, mediaLangCode);
								preparedStmt10B.setString(5, mediaCode);
								preparedStmt10B.executeUpdate();
								preparedStmt10B.close();
								preparedStmt10B = null;
							}
						}
						rs8.close(); 
						rs8 = null;
						preparedStmt8.close();
						preparedStmt8 = null;
					} 
				}
				else // if medias table entry is to be deleted
				{
					String controlParmStr = (String) controlParmObj;
					Object attr=null;
					String mediaCode=null;
					attr = event.getProperty(MEDIA_CODE);
					if (attr != null) mediaCode = (String) attr;
					if (controlParmStr.trim().equals(DELETE_OPT))
					{
						// Delete the actual media file also from hard disk
						preparedStmt11 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsQuery);
						preparedStmt11.setString(1, mediaCode);
						rs11 = preparedStmt11.executeQuery();
						String absMediaPath = null;
						if (rs11.next() == true)
						{
							absMediaPath = rs11.getString(3);
						}
						rs11.close();
						rs11 = null;
						preparedStmt11.close();
						preparedStmt11 = null;
						File fileToBeDeleted = new File(absMediaPath.trim());
						if (fileToBeDeleted != null && fileToBeDeleted.exists())
						{
							fileToBeDeleted.delete();
						}
						
						// Now delete the entry in database
						preparedStmt11 = dbConnectionPool.getPreparedStmt(conn, mediaDescriptorsDelete);
						preparedStmt11.setString(1, mediaCode);
						preparedStmt11.executeUpdate();
						preparedStmt11.close();
						preparedStmt11 = null;
						
						preparedStmt11A = dbConnectionPool.getPreparedStmt(conn, prevSuccessfulDownloadDelete);
						preparedStmt11A.setString(1, mediaCode);
						preparedStmt11A.executeUpdate();
						preparedStmt11A.close();
						preparedStmt11A = null;
						/*
						 * Since triggerDescMediaComponents table contains "not normal" media entries also, 
						 * ie. with no mediaCode, we could not have a foreign key constraint
						 * with mediaDescriptors table. So explicit delete is required.
						 */
						preparedStmt12 = dbConnectionPool.getPreparedStmt(conn, triggerDescMediaComponentsDelete);
						preparedStmt12.setString(1, mediaCode);
						preparedStmt12.executeUpdate();
						preparedStmt12.close();
						preparedStmt12 = null;
					}
				}
			}
			else
			{
				throw new Exception("Update type is neither 'media' nor 'media_plan'");
				
			}
			//Delete all nodes not scheduled in the current plan update
			preparedStmt12A = dbConnectionPool.getPreparedStmt(conn, triggerDescriptorsQuery);
			preparedStmt12A.setString(1, routeScheduleCode);
			rs12A = preparedStmt12A.executeQuery();
			while (rs12A.next() == true)
			{
				String currentlyScheduledNodeCode = rs12A.getString(1);
				if (!mediaScheduledNodes.contains(currentlyScheduledNodeCode))
				{
					preparedStmt12B = dbConnectionPool.getPreparedStmt(conn, triggerDescriptorsDelete);
					preparedStmt12B.setString(1, routeScheduleCode);
					preparedStmt12B.setString(2, currentlyScheduledNodeCode);
					preparedStmt12B.executeUpdate();
					preparedStmt12B.close();
					preparedStmt12B = null;
				}
			}
			rs12A.close();
			rs12A = null;
			preparedStmt12A.close();
			preparedStmt12A = null;
			
			preparedStmt13 = dbConnectionPool.getPreparedStmt(conn, routeSchedulesUpdate);
			preparedStmt13.setString(1, routeScheduleCode);
			preparedStmt13.executeUpdate();
			preparedStmt13.close();
			preparedStmt13 = null;
			conn.commit();
		}
 		catch (Exception e) 
 		{
 			System.out.println("Failed to update mediaInfo in Database" + e);
 			e.printStackTrace();
 		}
 		finally
 		{
 	  		if (rs != null)
    		{
    			try {rs.close();} catch (SQLException e) {} 
    			rs = null;
    		}
 			if (rs1 != null)
    		{
    			try {rs1.close();} catch (SQLException e) {} 
    			rs1 = null;
    		}
 			if (rs2 != null)
    		{
    			try {rs2.close();} catch (SQLException e) {} 
    			rs2 = null;
    		}
 			if (rs3A != null)
    		{
    			try {rs3A.close();} catch (SQLException e) {} 
    			rs3A = null;
    		}
	  		if (rs6 != null)
    		{
    			try {rs6.close();} catch (SQLException e) {} 
    			rs6 = null;
    		}
	  		if (rs6A != null)
    		{
    			try {rs6A.close();} catch (SQLException e) {} 
    			rs6A = null;
    		}
	  		if (rs8 != null)
    		{
    			try {rs8.close();} catch (SQLException e) {} 
    			rs8 = null;
    		}
	  		if (rs9 != null)
    		{
    			try {rs9.close();} catch (SQLException e) {} 
    			rs9 = null;
    		}
	  		if (rs11 != null)
    		{
    			try {rs11.close();} catch (SQLException e) {} 
    			rs11 = null;
    		}
	  		if (rs12A != null)
    		{
    			try {rs12A.close();} catch (SQLException e) {} 
    			rs12A = null;
    		}
    		if (preparedStmt != null)
    		{
    			try {preparedStmt.close();} catch (SQLException e) {} 
    			preparedStmt = null;
    		}
    		if (preparedStmtA != null)
    		{
    			try {preparedStmtA.close();} catch (SQLException e) {} 
    			preparedStmtA = null;
    		}
    		if (preparedStmt1 != null)
    		{
    			try {preparedStmt1.close();} catch (SQLException e) {} 
    			preparedStmt1 = null;
    		}
    		if (preparedStmt2 != null)
    		{
    			try {preparedStmt2.close();} catch (SQLException e) {} 
    			preparedStmt2 = null;
    		}
    		if (preparedStmt3 != null)
    		{
    			try {preparedStmt3.close();} catch (SQLException e) {} 
    			preparedStmt3 = null;
    		}
     		if (preparedStmt3A != null)
    		{
    			try {preparedStmt3A.close();} catch (SQLException e) {} 
    			preparedStmt3A = null;
    		}
     		if (preparedStmt3B != null)
    		{
    			try {preparedStmt3B.close();} catch (SQLException e) {} 
    			preparedStmt3B = null;
    		}
    		if (preparedStmt4 != null)
    		{
    			try {preparedStmt4.close();} catch (SQLException e) {} 
    			preparedStmt4 = null;
    		}
    		if (preparedStmt5 != null)
    		{
    			try {preparedStmt5.close();} catch (SQLException e) {} 
    			preparedStmt5 = null;
    		}
    		if (preparedStmt6 != null)
    		{
    			try {preparedStmt6.close();} catch (SQLException e) {} 
    			preparedStmt6 = null;
    		}
    		if (preparedStmt6A != null)
    		{
    			try {preparedStmt6A.close();} catch (SQLException e) {} 
    			preparedStmt6A = null;
    		}
    		if (preparedStmt6B != null)
    		{
    			try {preparedStmt6B.close();} catch (SQLException e) {} 
    			preparedStmt6B = null;
    		}
    		if (preparedStmt7 != null)
    		{
    			try {preparedStmt7.close();} catch (SQLException e) {} 
    			preparedStmt7 = null;
    		}
    		if (preparedStmt8 != null)
    		{
    			try {preparedStmt8.close();} catch (SQLException e) {} 
    			preparedStmt8 = null;
    		}
    		if (preparedStmt9 != null)
    		{
    			try {preparedStmt9.close();} catch (SQLException e) {} 
    			preparedStmt9 = null;
    		}
    		if (preparedStmt10 != null)
    		{
    			try {preparedStmt10.close();} catch (SQLException e) {} 
    			preparedStmt10 = null;
    		}
    		if (preparedStmt10A != null)
    		{
    			try {preparedStmt10A.close();} catch (SQLException e) {} 
    			preparedStmt10A = null;
    		}
    		if (preparedStmt10B != null)
    		{
    			try {preparedStmt10B.close();} catch (SQLException e) {} 
    			preparedStmt10B = null;
    		}
    		if (preparedStmt11 != null)
    		{
    			try {preparedStmt11.close();} catch (SQLException e) {} 
    			preparedStmt11 = null;
    		}
    		if (preparedStmt11A != null)
    		{
    			try {preparedStmt11A.close();} catch (SQLException e) {} 
    			preparedStmt11A = null;
    		}
    		if (preparedStmt12 != null)
    		{
    			try {preparedStmt12.close();} catch (SQLException e) {} 
    			preparedStmt12 = null;
    		}
    		if (preparedStmt12A != null)
    		{
    			try {preparedStmt12A.close();} catch (SQLException e) {} 
    			preparedStmt12A = null;
    		}
    		if (preparedStmt12 != null)
    		{
    			try {preparedStmt12B.close();} catch (SQLException e) {} 
    			preparedStmt12B = null;
    		}
    		if (preparedStmt13 != null)
    		{
    			try {preparedStmt13.close();} catch (SQLException e) {} 
    			preparedStmt13 = null;
    		}
			if (conn != null)
			{
				try {conn.close();} catch (SQLException e) {}
				conn = null;
			}
 		}
	}
}
